package com.r7data.dbstruct.meta;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.r7data.dbstruct.code.DbConstantCode;
import com.r7data.dbstruct.entity.DbConnectionInfo;


public class OracleDbMeta extends BaseDbMeta {
	
	/**
	 * 表查询 sql 模板
	 */
	public String getTableSearchSqlTmp() {
		this.tableSearchSqlTmp = "SELECT * FROM %s.%s WHERE ROWNUM < 1";
		return tableSearchSqlTmp;
	}
	
	/**
	 * oracle 获取 schemas
	 * @param con
	 * @return
	 */
	public List<String> getSchemas(Connection con){
		List<String> dbList = new ArrayList<String>();
		
		/* 该语句用于过滤系统 schema，是否启用后续待定
		String sql = select username from dba_users where username not in ('SYS','SYSTEM','MGMT_VIEW','DBSNMP','SYSMAN'
				,'SDE','OUTLN','MDSYS','WMSYS','FLOWS_FILES','ORDDATA','CTXSYS','ANONYMOUS','SI_INFORMTN_SCHEMA'
				,'ORDSYS','EXFSYS','APPQOSSYS','XDB','ORDPLUGINS','OWBSYS','OLAPSYS','XS$NULL','APEX_PUBLIC_USER'
				,'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR')order by username
		*/
		
		ResultSet rst = null;
		
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			
			rst = dbmd.getSchemas();

			while (rst != null && rst.next()) {
				
				String dbName = rst.getString(1);
				
				dbList.add(dbName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return dbList;
	}
	
	
	/**
	 * 获得 所有的 databases
	 */
	@Override
	public List<String> getDatabases(Connection con){

		List<String> dbList = getSchemas(con);
		
		return dbList;
	}
	
	/**
	 * 获取 db url
	 * @param dbInfo
	 * @return
	 */
	@Override
	public String getDbUrl(DbConnectionInfo dbInfo){
		String ipAddress = dbInfo.getIpAddress();
		int prot = dbInfo.getPort();
		String sid = dbInfo.getSid();
		
		//jdbc:oracle:thin:@172.16.6.215:1521/orcl
		String urlTemplate = "jdbc:oracle:thin:@%s:%d/%s";
		
		String url = String.format(urlTemplate, ipAddress, prot, sid);
		
		return url;
	}
	
	/**
	 * 特殊字符处理
	 * @param field
	 * @return
	 */
	public String escapeField(String field){
		String result = null;
		
		String tmp = "\"%s\"";
		if(field != null){
			result = String.format(tmp, field);
		}
		return result;
	}

	/**
	 * 获取表名 resultset
	 * @param dbmd
	 * @param schemaName
	 * @return
	 * @throws Exception
	 */
	@Override
	public ResultSet getTableResult(DatabaseMetaData dbmd, String schemaName) throws SQLException{
		
		ResultSet rst = dbmd.getTables(null, schemaName, null, DbConstantCode.table_type_table);
		
		return rst;
	}
	
	/**
	 * 获取视图名 resultset
	 * @param dbmd
	 * @param schemaName
	 * @return
	 * @throws Exception
	 */
	@Override
	public ResultSet getViewResult(DatabaseMetaData dbmd, String schemaName) throws SQLException{
		
		ResultSet rst = dbmd.getTables(null, schemaName, null, DbConstantCode.table_type_view);
		
		return rst;
	}
	
}


